﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PurchaseRequestDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PurchaseRequestDAL()
        {

        }

        public PurchaseRequestInfo GetPurchaseRequest(int Seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            PurchaseRequestInfo info = new PurchaseRequestInfo(); ;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_pr_seq", Seq);

                strSQL = "SELECT pr.* "
                            + ", ru.ICM_PC_RULE_NAME"
                            + ", yt.YEAR_TYPE_NAME"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", pm.BIDDING_STATUS"
                            + ", pm.ICM_PC_METHOD_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                            + ", dt.ICM_DUR_TYPE_NAME"
                            + ", dc.ICM_DUR_COUNT_NAME"
                            + ", CONCAT(pm.ICM_PC_METHOD_NAME, pt.ICM_PC_TYPE_NAME) AS PURCHASE_TYPE"
                        + " FROM ICM_PURCHASE_REQUEST pr"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON pr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON pr.RECORD_STATUS = rs.RECORD_STATUS"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pt.ICM_PC_TYPE_SEQ = pr.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_RULE ru ON pr.ICM_PC_RULE_SEQ = ru.ICM_PC_RULE_SEQ"
                            + " LEFT JOIN ICM_MAS_PURCHASE_METHOD pm ON pm.ICM_PC_METHOD_SEQ = pr.ICM_PC_METHOD_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON dt.ICM_DUR_TYPE_SEQ = pr.ICM_DUR_TYPE_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_COUNT dc ON dc.ICM_DUR_COUNT_SEQ = pr.ICM_DUR_COUNT_SEQ"
                        + " WHERE pr.ICM_PR_SEQ = @icm_pr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    MasterPurchaseRuleInfo infoPurchaseRule = new MasterPurchaseRuleInfo();
                    SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                    MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                    MasterPurchaseMethodInfo infoPurchaseMethod = new MasterPurchaseMethodInfo();
                    SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                    HrmEmployeeInfo infoPurchaseEmp = new HrmEmployeeInfo();
                    HrmJobSiteInfo infoPurchaseJobSite = new HrmJobSiteInfo();
                    HrmEmployeeInfo infoConfirmEmp = new HrmEmployeeInfo();
                    HrmEmployeeInfo infoCancelEmp = new HrmEmployeeInfo();
                    SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                    SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                    SysProgressInfo infoProgress = new SysProgressInfo();
                    SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();
                    RecordInfo infoRecord = new RecordInfo();

                    info.Seq = Convert.ToInt32(dr[0]["ICM_PR_SEQ"]);
                    info.Ref_No = (dr[0]["ICM_PR_REF_NO"]).ToString();
                    info.Name = (dr[0]["ICM_PR_NAME"]).ToString();
                    info.Reason = (dr[0]["ICM_PR_REASON"]).ToString();
                    info.Remark = (dr[0]["ICM_PR_REMARK"]).ToString();
                    info.PurchaseType = (dr[0]["PURCHASE_TYPE"]).ToString();
                    infoPurchaseRule.Seq = (Convert.IsDBNull(dr[0]["ICM_PC_RULE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PC_RULE_SEQ"]));
                    infoPurchaseRule.Name = (dr[0]["ICM_PC_RULE_NAME"]).ToString();
                    infoPprType.Seq = (Convert.IsDBNull(dr[0]["ICM_PPR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PPR_TYPE_SEQ"]));
                    infoPurchaseType.Seq = (Convert.IsDBNull(dr[0]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PC_TYPE_SEQ"]));
                    infoPurchaseType.Name = (dr[0]["ICM_PC_TYPE_NAME"]).ToString();
                    infoPurchaseMethod.Seq = (Convert.IsDBNull(dr[0]["ICM_PC_METHOD_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PC_METHOD_SEQ"]));
                    infoPurchaseMethod.Name = (dr[0]["ICM_PC_METHOD_NAME"]).ToString();
                    infoPurchaseMethod.Bidding_status = (dr[0]["BIDDING_STATUS"]).ToString();
                    info.MethodReason = dr[0]["ICM_PC_METHOD_REASON"].ToString();
                    infoYearType.Seq = (Convert.IsDBNull(dr[0]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["YEAR_TYPE_SEQ"]));
                    infoYearType.Name = dr[0]["YEAR_TYPE_NAME"].ToString();
                    info.BgmYear = (Convert.IsDBNull(dr[0]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[0]["BGM_YEAR"]));
                    info.PurchaseDate = (Convert.IsDBNull(dr[0]["PURCHASE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["purchase_date"]));
                    infoPurchaseEmp.Seq = (Convert.IsDBNull(dr[0]["PURCHASE_EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["PURCHASE_EMP_SEQ"]));
                    infoPurchaseJobSite.Seq = (Convert.IsDBNull(dr[0]["PURCHASE_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[0]["PURCHASE_JOB_SITE_SEQ"]));
                    info.ConfirmDate = (Convert.IsDBNull(dr[0]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["CONFIRM_DATE"]));
                    infoConfirmEmp.Seq = (Convert.IsDBNull(dr[0]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["CONFIRM_EMP_SEQ"]));
                    info.CancelDate = (Convert.IsDBNull(dr[0]["CANCEL_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["CANCEL_DATE"]));
                    infoCancelEmp.Seq = (Convert.IsDBNull(dr[0]["CANCEL_EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["CANCEL_EMP_SEQ"]));
                    info.RequireDate = (Convert.IsDBNull(dr[0]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["REQUIRE_DATE"]));
                    info.RequireDuration = (Convert.IsDBNull(dr[0]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[0]["REQUIRE_DURATION"]));
                    infoDurationType.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_TYPE_SEQ"]));
                    infoDurationType.Name = dr[0]["ICM_DUR_TYPE_NAME"].ToString();
                    infoDurationCount.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_COUNT_SEQ"]));
                    infoDurationCount.Name = dr[0]["ICM_DUR_COUNT_NAME"].ToString();
                    infoProgress.Seq = (Convert.IsDBNull(dr[0]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PROG_SEQ"]));
                    infoRecordStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    infoRecordStatus.Name = (dr[0]["RECORD_STATUS_NAME"]).ToString();
                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    info.ApprovedWhen = (Convert.IsDBNull(dr[0]["approved_when"]) ? null : (Nullable<DateTime>)(dr[0]["approved_when"]));

                    info.InfoPurchaseRule = infoPurchaseRule;
                    info.InfoPprType = infoPprType;
                    info.InfoPurchaseType = infoPurchaseType;
                    info.InfoPurchaseMethod = infoPurchaseMethod;
                    info.InfoYearType = infoYearType;
                    info.InfoPurchaseEmp = infoPurchaseEmp;
                    info.InfoPurchaseJobSite = infoPurchaseJobSite;
                    info.InfoConfirmEmp = infoConfirmEmp;
                    info.InfoCancelEmp = infoCancelEmp;
                    info.InfoDurationType = infoDurationType;
                    info.InfoDurationCount = infoDurationCount;
                    info.InfoProgress = infoProgress;
                    info.InfoRecordStatus = infoRecordStatus;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public PurchaseRequestInfo GetPurchaseRequestByBidder(int BidderSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            PurchaseRequestInfo info = new PurchaseRequestInfo(); ;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_bidder_seq", BidderSeq);

                strSQL = "SELECT pr.* " +
                        " , yt.year_type_name" +
                        " , pt.ICM_PC_TYPE_NAME" +
                        " , pm.bidding_status" +
                        " , pm.ICM_PC_METHOD_NAME" +
                        " , rs.RECORD_STATUS_NAME" +
                        " , dt.icm_dur_type_name" +
                        " , dc.icm_dur_count_name" +
                        " FROM ICM_PURCHASE_REQUEST pr" +
                        " INNER JOIN sys_year_type yt ON pr.year_type_seq = yt.year_type_seq" +
                        " INNER JOIN SYS_RECORD_STATUS rs ON pr.RECORD_STATUS = rs.RECORD_STATUS" +
                        " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pt.ICM_PC_TYPE_SEQ = pr.ICM_PC_TYPE_SEQ" +
                        " LEFT JOIN ICM_MAS_PURCHASE_METHOD pm ON pm.ICM_PC_METHOD_SEQ = pr.ICM_PC_METHOD_SEQ" +
                        " LEFT JOIN icm_sys_duration_type dt ON dt.icm_dur_type_seq = pr.icm_dur_type_seq" +
                        " LEFT JOIN icm_sys_duration_count dc ON dc.icm_dur_count_seq = pr.icm_dur_count_seq" +
                        " inner join icm_bidder b ON pr.icm_pr_seq = b.icm_pr_seq " +
                        " WHERE b.icm_bidder_seq = @icm_bidder_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {

                    MasterPurchaseRuleInfo infoPurchaseRule = new MasterPurchaseRuleInfo();
                    SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                    MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                    MasterPurchaseMethodInfo infoPurchaseMethod = new MasterPurchaseMethodInfo();
                    SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                    HrmEmployeeInfo infoPurchaseEmp = new HrmEmployeeInfo();
                    HrmJobSiteInfo infoPurchaseJobSite = new HrmJobSiteInfo();
                    HrmEmployeeInfo infoConfirmEmp = new HrmEmployeeInfo();
                    HrmEmployeeInfo infoCancelEmp = new HrmEmployeeInfo();
                    SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                    SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                    SysProgressInfo infoProgress = new SysProgressInfo();
                    SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();
                    RecordInfo infoRecord = new RecordInfo();

                    info.Seq = Convert.ToInt32(dr[0]["icm_pr_seq"]);
                    info.Ref_No = (dr[0]["icm_pr_ref_no"]).ToString();
                    info.Name = (dr[0]["icm_pr_name"]).ToString();
                    info.Reason = (dr[0]["icm_pr_reason"]).ToString();
                    infoPurchaseRule.Seq = (Convert.IsDBNull(dr[0]["icm_pc_rule_seq"]) ? null : (Nullable<int>)(dr[0]["icm_pc_rule_seq"]));
                    infoPprType.Seq = (Convert.IsDBNull(dr[0]["icm_ppr_type_seq"]) ? null : (Nullable<int>)(dr[0]["icm_ppr_type_seq"]));
                    infoPurchaseType.Seq = (Convert.IsDBNull(dr[0]["icm_pc_type_seq"]) ? null : (Nullable<int>)(dr[0]["icm_pc_type_seq"]));
                    infoPurchaseType.Name = (dr[0]["ICM_PC_TYPE_NAME"]).ToString();
                    infoPurchaseMethod.Seq = (Convert.IsDBNull(dr[0]["icm_pc_method_seq"]) ? null : (Nullable<int>)(dr[0]["icm_pc_method_seq"]));
                    infoPurchaseMethod.Name = (dr[0]["ICM_PC_METHOD_NAME"]).ToString();
                    infoPurchaseMethod.Bidding_status = (dr[0]["bidding_status"]).ToString();
                    info.MethodReason = dr[0]["icm_pc_method_reason"].ToString();
                    infoYearType.Seq = (Convert.IsDBNull(dr[0]["year_type_seq"]) ? null : (Nullable<int>)(dr[0]["year_type_seq"]));
                    infoYearType.Name = dr[0]["year_type_name"].ToString();
                    info.BgmYear = (Convert.IsDBNull(dr[0]["bgm_year"]) ? null : (Nullable<int>)(dr[0]["bgm_year"]));
                    info.PurchaseDate = (Convert.IsDBNull(dr[0]["purchase_date"]) ? null : (Nullable<DateTime>)(dr[0]["purchase_date"]));
                    infoPurchaseEmp.Seq = (Convert.IsDBNull(dr[0]["purchase_emp_seq"]) ? null : (Nullable<int>)(dr[0]["purchase_emp_seq"]));
                    infoPurchaseJobSite.Seq = (Convert.IsDBNull(dr[0]["purchase_job_site_seq"]) ? null : (Nullable<int>)(dr[0]["purchase_job_site_seq"]));
                    info.ConfirmDate = (Convert.IsDBNull(dr[0]["confirm_date"]) ? null : (Nullable<DateTime>)(dr[0]["confirm_date"]));
                    infoConfirmEmp.Seq = (Convert.IsDBNull(dr[0]["confirm_emp_seq"]) ? null : (Nullable<int>)(dr[0]["confirm_emp_seq"]));
                    info.CancelDate = (Convert.IsDBNull(dr[0]["cancel_date"]) ? null : (Nullable<DateTime>)(dr[0]["cancel_date"]));
                    infoCancelEmp.Seq = (Convert.IsDBNull(dr[0]["cancel_emp_seq"]) ? null : (Nullable<int>)(dr[0]["cancel_emp_seq"]));
                    info.RequireDate = (Convert.IsDBNull(dr[0]["require_date"]) ? null : (Nullable<DateTime>)(dr[0]["require_date"]));
                    info.RequireDuration = (Convert.IsDBNull(dr[0]["require_duration"]) ? null : (Nullable<int>)(dr[0]["require_duration"]));
                    infoDurationType.Seq = (Convert.IsDBNull(dr[0]["icm_dur_type_seq"]) ? null : (Nullable<int>)(dr[0]["icm_dur_type_seq"]));
                    infoDurationType.Name = dr[0]["icm_dur_type_name"].ToString();
                    infoDurationCount.Seq = (Convert.IsDBNull(dr[0]["icm_dur_count_seq"]) ? null : (Nullable<int>)(dr[0]["icm_dur_count_seq"]));
                    infoDurationCount.Name = dr[0]["icm_dur_count_name"].ToString();
                    infoProgress.Seq = (Convert.IsDBNull(dr[0]["icm_prog_seq"]) ? null : (Nullable<int>)(dr[0]["icm_prog_seq"]));
                    infoRecordStatus.Status = (dr[0]["record_status"]).ToString();
                    infoRecordStatus.Name = (dr[0]["RECORD_STATUS_NAME"]).ToString();
                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    info.ApprovedWhen = (Convert.IsDBNull(dr[0]["approved_when"]) ? null : (Nullable<DateTime>)(dr[0]["approved_when"]));

                    info.InfoPurchaseRule = infoPurchaseRule;
                    info.InfoPprType = infoPprType;
                    info.InfoPurchaseType = infoPurchaseType;
                    info.InfoPurchaseMethod = infoPurchaseMethod;
                    info.InfoYearType = infoYearType;
                    info.InfoPurchaseEmp = infoPurchaseEmp;
                    info.InfoPurchaseJobSite = infoPurchaseJobSite;
                    info.InfoConfirmEmp = infoConfirmEmp;
                    info.InfoCancelEmp = infoCancelEmp;
                    info.InfoDurationType = infoDurationType;
                    info.InfoDurationCount = infoDurationCount;
                    info.InfoProgress = infoProgress;
                    info.InfoRecordStatus = infoRecordStatus;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public PurchaseRequestInfo GetPurchaseRequestUrgent(int Seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_pr_seq", Seq);

                strSQL = "SELECT icm_purchase_request.icm_pr_seq," +
                            " icm_purchase_request.year_type_seq," +
                            " icm_purchase_request.bgm_year," +
                            " sys_year_type.year_type_name," +
                            " icm_purchase_request.icm_pc_rule_seq," +
                            " icm_mas_purchase_rule.icm_pc_rule_name," +
                            " icm_purchase_request.icm_pc_type_seq," +
                            " icm_mas_purchase_type.icm_pc_type_name," +
                            " icm_purchase_request.purchase_date," +
                            " icm_purchase_request.icm_pr_name," +
                            " icm_purchase_request.icm_pr_reason," +
                            " icm_purchase_request.icm_pc_method_seq," +
                            " icm_mas_purchase_method.icm_pc_method_name," +
                            " icm_purchase_request.icm_pc_method_reason," +
                            " sys_record_status.record_status_name," +
                            " sys_record_status.record_status," +
                            " icm_sys_progress.icm_prog_name" +
                        " FROM" +
                            " icm_purchase_request" +
                            " Inner Join sys_year_type ON icm_purchase_request.year_type_seq = sys_year_type.year_type_seq" +
                            " Inner Join icm_mas_purchase_rule ON icm_purchase_request.icm_pc_rule_seq = icm_mas_purchase_rule.icm_pc_rule_seq" +
                            " Inner Join icm_mas_purchase_type ON icm_purchase_request.icm_pc_type_seq = icm_mas_purchase_type.icm_pc_type_seq" +
                            " Left Join icm_mas_purchase_method ON icm_purchase_request.icm_pc_method_seq = icm_mas_purchase_method.icm_pc_method_seq" +
                            " Left Join sys_record_status ON icm_purchase_request.record_status = sys_record_status.record_status" +
                            " Left Join icm_sys_progress ON icm_purchase_request.icm_prog_seq = icm_sys_progress.icm_prog_seq" +
                        " WHERE icm_purchase_request.icm_pr_seq = @icm_pr_seq and  icm_purchase_request.record_status <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                PurchaseRequestInfo info = new PurchaseRequestInfo();
                if (dr.Count > 0)
                {
                    MasterPurchaseRuleInfo infoPurchaseRule = new MasterPurchaseRuleInfo();
                    MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                    MasterPurchaseMethodInfo infoPurchaseMethod = new MasterPurchaseMethodInfo();
                    SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                    SysRecordStatusInfo infoRecordStstus = new SysRecordStatusInfo();
                    SysProgressInfo infoProgress = new SysProgressInfo();

                    info.Seq = Convert.ToInt32(dr[0]["icm_pr_seq"]);
                    infoYearType.Seq = (Convert.IsDBNull(dr[0]["year_type_seq"]) ? null : (Nullable<int>)(dr[0]["year_type_seq"]));
                    infoYearType.Name = dr[0]["year_type_name"].ToString();
                    info.BgmYear = (Convert.IsDBNull(dr[0]["bgm_year"]) ? null : (Nullable<int>)(dr[0]["bgm_year"]));
                    infoPurchaseRule.Seq = (Convert.IsDBNull(dr[0]["icm_pc_rule_seq"]) ? null : (Nullable<int>)(dr[0]["icm_pc_rule_seq"]));
                    infoPurchaseRule.Name = dr[0]["icm_pc_rule_name"].ToString();
                    infoPurchaseType.Seq = (Convert.IsDBNull(dr[0]["icm_pc_type_seq"]) ? null : (Nullable<int>)(dr[0]["icm_pc_type_seq"]));
                    infoPurchaseType.Name = (dr[0]["icm_pc_type_name"]).ToString();
                    info.PurchaseDate = (Convert.IsDBNull(dr[0]["purchase_date"]) ? null : (Nullable<DateTime>)(dr[0]["purchase_date"]));
                    info.Name = (dr[0]["icm_pr_name"]).ToString();
                    info.Reason = (dr[0]["icm_pr_reason"]).ToString();
                    infoPurchaseMethod.Seq = (Convert.IsDBNull(dr[0]["icm_pc_method_seq"]) ? null : (Nullable<int>)(dr[0]["icm_pc_method_seq"]));
                    infoPurchaseMethod.Name = (dr[0]["ICM_PC_METHOD_NAME"]).ToString();
                    info.MethodReason = (dr[0]["icm_pc_method_reason"]).ToString();
                    infoRecordStstus.Name = (dr[0]["record_status_name"]).ToString();
                    infoRecordStstus.Status = (dr[0]["record_status"]).ToString();
                    infoProgress.Name = (dr[0]["icm_prog_name"]).ToString();

                    info.InfoPurchaseRule = infoPurchaseRule;
                    info.InfoPurchaseType = infoPurchaseType;
                    info.InfoPurchaseMethod = infoPurchaseMethod;
                    info.InfoYearType = infoYearType;
                    info.InfoRecordStatus = infoRecordStstus;
                    info.InfoProgress = infoProgress;
                }
                return info;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<PurchaseRequestInfo> GetPurchaseRequest(int YearTypeSeq,int Year,Nullable<int> JobSiteSeq, Nullable<int> PurchaseTypeSeq, Nullable<int> PurchaseMethodSeq, string OperationName, string RecordStatus,int PprTypeSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestInfo> infoList = new List<PurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@year_type_seq", YearTypeSeq);
                dbManager.AddParameters(2, "@bgm_year", Year);
                dbManager.AddParameters(3, "@icm_ppr_type_seq", PprTypeSeq);

                strSQL = "SELECT pr.* " +
                            " , pt.ICM_PC_TYPE_NAME" +
                            " , pm.ICM_PC_METHOD_NAME" +
                            " , rs.RECORD_STATUS_NAME" +
                            ", CAST(CONCAT(yt.YEAR_TYPE_NAME,' ', pr.BGM_YEAR) AS CHAR) AS YEAR_TYPE_DESCR" +
                        " FROM ICM_PURCHASE_REQUEST pr" +
                            " INNER JOIN SYS_RECORD_STATUS rs ON pr.RECORD_STATUS = rs.RECORD_STATUS" +
                            " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pt.ICM_PC_TYPE_SEQ = pr.ICM_PC_TYPE_SEQ" +
                            " INNER JOIN SYS_YEAR_TYPE yt ON pr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ" + 
                            " LEFT JOIN ICM_MAS_PURCHASE_METHOD pm ON pm.ICM_PC_METHOD_SEQ = pr.ICM_PC_METHOD_SEQ" +
                        " WHERE pr.icm_ppr_type_seq = @icm_ppr_type_seq and pr.RECORD_STATUS <> @delete_status and pr.year_type_seq = @year_type_seq and pr.bgm_year = @bgm_year ";
                strSQL += (JobSiteSeq != null) ? " and pr.purchase_job_site_seq = " + JobSiteSeq : "";
                strSQL += (PurchaseTypeSeq != null) ? " and pr.icm_ppr_type_seq = " + PurchaseTypeSeq : "";
                strSQL += (PurchaseMethodSeq != null) ? " and pr.icm_pc_method_seq = " + PurchaseMethodSeq : "";
                strSQL += (OperationName != "") ? " and pr.icm_pc_method_seq = '" + OperationName + "'" : "";
                strSQL += (RecordStatus != "") ? " and pr.record_status = '" + RecordStatus + "'" : "";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestInfo info = new PurchaseRequestInfo();
                        MasterPurchaseRuleInfo infoPurchaseRule = new MasterPurchaseRuleInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        MasterPurchaseMethodInfo infoPurchaseMethod = new MasterPurchaseMethodInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                        HrmEmployeeInfo infoPurchaseEmp = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoPurchaseJobSite = new HrmJobSiteInfo();
                        HrmEmployeeInfo infoConfirmEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoCancelEmp = new HrmEmployeeInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                        SysProgressInfo infoProgress = new SysProgressInfo();
                        SysRecordStatusInfo infoRecordStatus = new SysRecordStatusInfo();
                        RecordInfo infoRecord = new RecordInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_pr_seq"]);
                        info.Ref_No = (dr[i]["icm_pr_ref_no"]).ToString();
                        info.Name = (dr[i]["icm_pr_name"]).ToString();
                        info.Reason = (dr[i]["icm_pr_reason"]).ToString();
                        info.YearTypeDescr = (dr[i]["YEAR_TYPE_DESCR"]).ToString();
                        infoPurchaseRule.Seq = (Convert.IsDBNull(dr[i]["icm_pc_rule_seq"]) ? null : (Nullable<int>)(dr[i]["icm_pc_rule_seq"]));
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["icm_ppr_type_seq"]) ? null : (Nullable<int>)(dr[i]["icm_ppr_type_seq"]));
                        infoPurchaseType.Seq = (Convert.IsDBNull(dr[i]["icm_pc_type_seq"]) ? null : (Nullable<int>)(dr[i]["icm_pc_type_seq"]));
                        infoPurchaseType.Name = (dr[i]["ICM_PC_TYPE_NAME"]).ToString();
                        infoPurchaseMethod.Seq = (Convert.IsDBNull(dr[i]["icm_pc_method_seq"]) ? null : (Nullable<int>)(dr[i]["icm_pc_method_seq"]));
                        infoPurchaseMethod.Name = (dr[i]["ICM_PC_METHOD_NAME"]).ToString();
                        info.MethodReason = dr[i]["icm_pc_method_reason"].ToString();
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["year_type_seq"]) ? null : (Nullable<int>)(dr[i]["year_type_seq"]));
                        info.BgmYear = (Convert.IsDBNull(dr[i]["bgm_year"]) ? null : (Nullable<int>)(dr[i]["bgm_year"]));
                        info.PurchaseDate = (Convert.IsDBNull(dr[i]["purchase_date"]) ? null : (Nullable<DateTime>)(dr[i]["purchase_date"]));
                        infoPurchaseEmp.Seq = (Convert.IsDBNull(dr[i]["purchase_emp_seq"]) ? null : (Nullable<int>)(dr[i]["purchase_emp_seq"]));
                        infoPurchaseJobSite.Seq = (Convert.IsDBNull(dr[i]["purchase_job_site_seq"]) ? null : (Nullable<int>)(dr[i]["purchase_job_site_seq"]));
                        info.ConfirmDate = (Convert.IsDBNull(dr[i]["confirm_date"]) ? null : (Nullable<DateTime>)(dr[i]["confirm_date"]));
                        infoConfirmEmp.Seq = (Convert.IsDBNull(dr[i]["confirm_emp_seq"]) ? null : (Nullable<int>)(dr[i]["confirm_emp_seq"]));
                        info.CancelDate = (Convert.IsDBNull(dr[i]["cancel_date"]) ? null : (Nullable<DateTime>)(dr[i]["cancel_date"]));
                        infoCancelEmp.Seq = (Convert.IsDBNull(dr[i]["cancel_emp_seq"]) ? null : (Nullable<int>)(dr[i]["cancel_emp_seq"]));
                        info.RequireDate = (Convert.IsDBNull(dr[i]["require_date"]) ? null : (Nullable<DateTime>)(dr[i]["require_date"]));
                        info.RequireDuration = (Convert.IsDBNull(dr[i]["require_duration"]) ? null : (Nullable<int>)(dr[i]["require_duration"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["icm_dur_type_seq"]) ? null : (Nullable<int>)(dr[i]["icm_dur_type_seq"]));
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["icm_dur_count_seq"]) ? null : (Nullable<int>)(dr[i]["icm_dur_count_seq"]));
                        infoProgress.Seq = (Convert.IsDBNull(dr[i]["icm_prog_seq"]) ? null : (Nullable<int>)(dr[i]["icm_prog_seq"]));
                        infoRecordStatus.Status = (dr[i]["record_status"]).ToString();
                        infoRecordStatus.Name = (dr[i]["RECORD_STATUS_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        info.ApprovedWhen = (Convert.IsDBNull(dr[i]["approved_when"]) ? null : (Nullable<DateTime>)(dr[i]["approved_when"]));

                        info.InfoPurchaseRule = infoPurchaseRule;
                        info.InfoPprType = infoPprType;
                        info.InfoPurchaseType = infoPurchaseType;
                        info.InfoPurchaseMethod = infoPurchaseMethod;
                        info.InfoYearType = infoYearType;
                        info.InfoPurchaseEmp = infoPurchaseEmp;
                        info.InfoPurchaseJobSite = infoPurchaseJobSite;
                        info.InfoConfirmEmp = infoConfirmEmp;
                        info.InfoCancelEmp = infoCancelEmp;
                        info.InfoDurationType = infoDurationType;
                        info.InfoDurationCount = infoDurationCount;
                        info.InfoProgress = infoProgress;
                        info.InfoRecordStatus = infoRecordStatus;
                        info.InfoRecord = infoRecord;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseRequestInfo> GetPurchaseRequestForBid(string search_text)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestInfo> infoList = new List<PurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@active_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@search_text", search_text);

                strSQL = "SELECT main.*"
                        + " FROM"
                        + "(SELECT pr.ICM_PR_SEQ"
                            + ", r.ICM_PC_RULE_NAME"
                            + ", CONCAT(pm.ICM_PC_METHOD_NAME, pt.ICM_PC_TYPE_NAME) AS PURCHASE_TYPE"
                            + ", pr.ICM_PR_REF_NO"
                            + ", pr.ICM_PR_NAME"
                            + ", FORMAT(coalesce((select sum(coalesce(icm_pr_item_qty,0) * coalesce(icm_pr_item_price,0)) from icm_purchase_request_item where icm_pr_seq = pr.icm_pr_seq),0),2) AS SUM_AMOUNT"
                            + ", CONCAT(em.FIRST_NAME, ' ' , em.LAST_NAME) AS EMP_NAME"
                        + " FROM ICM_PURCHASE_REQUEST pr"
                            + " INNER JOIN HRM_PS_EMPLOYEES em ON pr.PURCHASE_EMP_SEQ = em.EMP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_RULE r ON pr.ICM_PC_RULE_SEQ = r.ICM_PC_RULE_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_METHOD pm ON pr.ICM_PC_METHOD_SEQ = pm.ICM_PC_METHOD_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON pr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                        + " WHERE pr.RECORD_STATUS = @approve_status"
                            + " AND pm.BIDDING_STATUS = @active_status"
                            + " AND pr.ICM_PR_SEQ NOT IN "
                                + " (select icm_pr_seq"
                                + " from icm_bid"
                                + " where record_status <> @delete_status"
                                    + " and icm_pr_seq is not null"
                                + ")"
                        + ") main"
                        + " WHERE CASE WHEN @search_text = '' THEN 0 = 0"
                                + " ELSE (ICM_PC_RULE_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR PURCHASE_TYPE LIKE CONCAT('%', @search_text , '%')"
                                    + " OR ICM_PR_REF_NO LIKE CONCAT('%', @search_text , '%')"
                                    + " OR ICM_PR_NAME LIKE CONCAT('%', @search_text , '%')"
                                    + " OR SUM_AMOUNT LIKE CONCAT('%', @search_text , '%')"
                                    + " OR EMP_NAME LIKE CONCAT('%', @search_text , '%')"
                                + ")"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestInfo info = new PurchaseRequestInfo();
                        HrmEmployeeInfo infoPurchaseEmp = new HrmEmployeeInfo();
                        MasterPurchaseRuleInfo infoPurchaseRule = new MasterPurchaseRuleInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PR_SEQ"]);
                        info.Ref_No = (dr[i]["ICM_PR_REF_NO"]).ToString();
                        info.Name = (dr[i]["ICM_PR_NAME"]).ToString();
                        infoPurchaseEmp.Name = (dr[i]["EMP_NAME"]).ToString();
                        infoPurchaseRule.Name = (dr[i]["ICM_PC_RULE_NAME"]).ToString();
                        info.PurchaseType = (dr[i]["PURCHASE_TYPE"]).ToString();
                        info.SumAmount = (dr[i]["SUM_AMOUNT"]).ToString();

                        info.InfoPurchaseRule = infoPurchaseRule;
                        info.InfoPurchaseEmp = infoPurchaseEmp;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;  
        }

        public void Insert(PurchaseRequestInfo Info)
        {

        }

        public void Delete(PurchaseRequestInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(3, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE  ICM_PURCHASE_REQUEST"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE icm_pr_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<PurchaseRequestInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PURCHASE_REQUEST"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PR_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(1, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " SET ICM_PR_ITEM_SEQ = NULL"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PR_ITEM_SEQ IN"
                        + " (select icm_pr_item_seq"
                        + " from icm_purchase_request_item"
                        + " where icm_pr_seq in (" + seq + ")"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ChangePurchaseEmployee(IList<PurchaseRequestInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(1, "@updated_when", infoList[0].InfoRecord.Updated_when);
                dbManager.AddParameters(2, "@purchase_emp", infoList[0].InfoPurchaseEmp.Seq);

                strSQL = "UPDATE ICM_PURCHASE_REQUEST"
                    + " SET PURCHASE_EMP_SEQ = @purchase_emp"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PR_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(PurchaseRequestInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(29);

                dbManager.AddParameters(0, "@icm_pr_seq", info.Seq);
                dbManager.AddParameters(1, "@icm_pr_ref_no", info.Ref_No);
                dbManager.AddParameters(2, "@icm_pr_name", info.Name);
                dbManager.AddParameters(3, "@icm_pr_reason", info.Reason);
                dbManager.AddParameters(4, "@icm_pc_rule_seq", info.InfoPurchaseRule);
                dbManager.AddParameters(5, "@icm_ppr_type_seq", info.InfoPprType);
                dbManager.AddParameters(6, "@icm_pc_type_seq", info.InfoPurchaseType);
                dbManager.AddParameters(7, "@icm_pc_method_seq", info.InfoPurchaseMethod);
                dbManager.AddParameters(8, "@icm_pc_method_reason", info.MethodReason);
                dbManager.AddParameters(9, "@year_type_seq", info.InfoYearType);
                dbManager.AddParameters(10, "@bgm_year", info.BgmYear);
                dbManager.AddParameters(11, "@purchase_date", info.PurchaseDate);
                dbManager.AddParameters(12, "@purchase_emp_seq", info.InfoPurchaseEmp);
                dbManager.AddParameters(13, "@purchase_job_site_seq", info.InfoPurchaseJobSite);
                dbManager.AddParameters(14, "@confirm_date", info.ConfirmDate);
                dbManager.AddParameters(15, "@confirm_emp_seq", info.InfoConfirmEmp);
                dbManager.AddParameters(16, "@cancel_date", info.CancelDate);
                dbManager.AddParameters(17, "@cancel_emp_seq", info.InfoCancelEmp);
                dbManager.AddParameters(18, "@require_date", info.RequireDate);
                dbManager.AddParameters(19, "@require_duration", info.RequireDuration);
                dbManager.AddParameters(20, "@icm_dur_type_seq", info.InfoDurationType);
                dbManager.AddParameters(21, "@icm_dur_count_seq", info.InfoDurationCount);
                dbManager.AddParameters(22, "@icm_prog_seq", info.InfoProgress);
                dbManager.AddParameters(23, "@record_status", info.InfoRecordStatus);
                dbManager.AddParameters(24, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(25, "@updated_when", info.InfoRecord.Updated_when);
                dbManager.AddParameters(26, "@restored_by", info.InfoRecord.Restored_by);
                dbManager.AddParameters(27, "@restored_when", info.InfoRecord.Restored_when);
                dbManager.AddParameters(28, "@approved_when", info.ApprovedWhen);

                strSQL = "UPDATE icm_purchase_request"
                    + " SET icm_pr_ref_no = @icm_pr_ref_no"
                    + ", icm_pr_name = @icm_pr_name"
                    + ", icm_pr_reason = @icm_pr_reason"
                    + ", icm_pc_rule_seq = @icm_pc_rule_seq"
                    + ", icm_ppr_type_seq = @icm_ppr_type_seq"
                    + ", icm_pc_type_seq = @icm_pc_type_seq"
                    + ", icm_pc_method_seq = @icm_pc_method_seq"
                    + ", icm_pc_method_reason = @icm_pc_method_reason"
                    + ", year_type_seq = @year_type_seq"
                    + ", bgm_year = @bgm_year"
                    + ", purchase_date = @purchase_date"
                    + ", purchase_emp_seq = @purchase_emp_seq"
                    + ", purchase_job_site_seq = @purchase_job_site_seq"
                    + ", confirm_date = @confirm_date"
                    + ", confirm_emp_seq = @confirm_emp_seq"
                    + ", cancel_date = @cancel_date"
                    + ", cancel_emp_seq = @cancel_emp_seq"
                    + ", require_date = @require_date"
                    + ", require_duration = @require_duration"
                    + ", icm_dur_type_seq = @icm_dur_type_seq"
                    + ", icm_dur_count_seq = @icm_dur_count_seq"
                    + ", icm_prog_seq = @icm_prog_seq"
                    + ", record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + ", restored_by = @restored_by"
                    + ", restored_when = @restored_when"
                    + ", approved_when = @approved_when"
                    + " WHERE icm_pr_seq = @icm_pr_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdatePurchaseMethod(int PcSeq, int MethodSeq, string MethodSeqreason)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@icm_pr_seq", PcSeq);
                dbManager.AddParameters(1, "@icm_pc_method_seq", MethodSeq);
                dbManager.AddParameters(2, "@icm_pc_method_reason", MethodSeqreason);
                

                strSQL = "UPDATE icm_purchase_request SET "
                    + " icm_pc_method_seq = @icm_pc_method_seq"
                    + ", icm_pc_method_reason = @icm_pc_method_reason"
                    + " WHERE icm_pr_seq = @icm_pr_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdatePurchaseRequest(PurchaseRequestInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@icm_pr_seq", info.Seq);
                dbManager.AddParameters(1, "@purchase_date", info.PurchaseDate);
                dbManager.AddParameters(2, "@icm_pr_name", info.Name);
                dbManager.AddParameters(3, "@icm_pr_reason", info.Reason);
                dbManager.AddParameters(4, "@icm_pc_method_seq", info.InfoPurchaseMethod.Seq);
                dbManager.AddParameters(5, "@icm_pc_method_reason", info.MethodReason);
                dbManager.AddParameters(6, "@require_duration", info.RequireDuration);
                dbManager.AddParameters(7, "@icm_dur_type_seq", info.InfoDurationType.Seq);
                dbManager.AddParameters(8, "@icm_dur_count_seq", info.InfoDurationCount.Seq);
                dbManager.AddParameters(9, "@require_date", info.RequireDate);
                dbManager.AddParameters(10, "@icm_pr_remark", info.Remark);

                strSQL = "UPDATE icm_purchase_request SET "
                    + " purchase_date = @purchase_date"
                    + ", icm_pr_name = @icm_pr_name"
                    + ", icm_pr_reason = @icm_pr_reason"
                    + ", icm_pr_remark = @icm_pr_remark"
                    + ", icm_pc_method_seq = @icm_pc_method_seq"
                    + ", icm_pc_method_reason = @icm_pc_method_reason"
                    + ", require_duration = @require_duration"
                    + ", icm_dur_type_seq = @icm_dur_type_seq"
                    + ", icm_dur_count_seq = @icm_dur_count_seq"
                    + ", require_date = @require_date"
                    + " WHERE icm_pr_seq = @icm_pr_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(IList<PurchaseRequestInfo> infoList)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(29);

                    dbManager.AddParameters(0, "@icm_pr_seq", infoList[i].Seq);
                    dbManager.AddParameters(1, "@icm_pr_ref_no", infoList[i].Ref_No);
                    dbManager.AddParameters(2, "@icm_pr_name", infoList[i].Name);
                    dbManager.AddParameters(3, "@icm_pr_reason", infoList[i].Reason);
                    dbManager.AddParameters(4, "@icm_pc_rule_seq", infoList[i].InfoPurchaseRule.Seq);
                    dbManager.AddParameters(5, "@icm_ppr_type_seq", infoList[i].InfoPprType.Seq);
                    dbManager.AddParameters(6, "@icm_pc_type_seq", infoList[i].InfoPurchaseType.Seq);
                    dbManager.AddParameters(7, "@icm_pc_method_seq", infoList[i].InfoPurchaseMethod.Seq);
                    dbManager.AddParameters(8, "@icm_pc_method_reason", infoList[i].MethodReason);
                    dbManager.AddParameters(9, "@year_type_seq", infoList[i].InfoYearType.Seq);
                    dbManager.AddParameters(10, "@bgm_year", infoList[i].BgmYear);
                    dbManager.AddParameters(11, "@purchase_date", infoList[i].PurchaseDate);
                    dbManager.AddParameters(12, "@purchase_emp_seq", infoList[i].InfoPurchaseEmp.Seq);
                    dbManager.AddParameters(13, "@purchase_job_site_seq", infoList[i].InfoPurchaseJobSite.Seq);
                    dbManager.AddParameters(14, "@confirm_date", infoList[i].ConfirmDate);
                    dbManager.AddParameters(15, "@confirm_emp_seq", infoList[i].InfoConfirmEmp.Seq);
                    dbManager.AddParameters(16, "@cancel_date", infoList[i].CancelDate);
                    dbManager.AddParameters(17, "@cancel_emp_seq", infoList[i].InfoCancelEmp.Seq);
                    dbManager.AddParameters(18, "@require_date", infoList[i].RequireDate);
                    dbManager.AddParameters(19, "@require_duration", infoList[i].RequireDuration);
                    dbManager.AddParameters(20, "@icm_dur_type_seq", infoList[i].InfoDurationType.Seq);
                    dbManager.AddParameters(21, "@icm_dur_count_seq", infoList[i].InfoDurationCount.Seq);
                    dbManager.AddParameters(22, "@icm_prog_seq", infoList[i].InfoProgress.Seq);
                    dbManager.AddParameters(23, "@record_status", infoList[i].InfoRecordStatus.Status);
                    dbManager.AddParameters(24, "@updated_by", infoList[i].InfoRecord.Updated_by);
                    dbManager.AddParameters(25, "@updated_when", infoList[i].InfoRecord.Updated_when);
                    dbManager.AddParameters(26, "@restored_by", infoList[i].InfoRecord.Restored_by);
                    dbManager.AddParameters(27, "@restored_when", infoList[i].InfoRecord.Restored_when);
                    dbManager.AddParameters(28, "@approved_when", infoList[i].ApprovedWhen);

                    strSQL = "UPDATE icm_purchase_request"
                        + " SET icm_pr_ref_no = @icm_pr_ref_no"
                        + ", icm_pr_name = @icm_pr_name"
                        + ", icm_pr_reason = @icm_pr_reason"
                        + ", icm_pc_rule_seq = @icm_pc_rule_seq"
                        + ", icm_ppr_type_seq = @icm_ppr_type_seq"
                        + ", icm_pc_type_seq = @icm_pc_type_seq"
                        + ", icm_pc_method_seq = @icm_pc_method_seq"
                        + ", icm_pc_method_reason = @icm_pc_method_reason"
                        + ", year_type_seq = @year_type_seq"
                        + ", bgm_year = @bgm_year"
                        + ", purchase_date = @purchase_date"
                        + ", purchase_emp_seq = @purchase_emp_seq"
                        + ", purchase_job_site_seq = @purchase_job_site_seq"
                        + ", confirm_date = @confirm_date"
                        + ", confirm_emp_seq = @confirm_emp_seq"
                        + ", cancel_date = @cancel_date"
                        + ", cancel_emp_seq = @cancel_emp_seq"
                        + ", require_date = @require_date"
                        + ", require_duration = @require_duration"
                        + ", icm_dur_type_seq = @icm_dur_type_seq"
                        + ", icm_dur_count_seq = @icm_dur_count_seq"
                        + ", icm_prog_seq = @icm_prog_seq"
                        + ", record_status = @record_status"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                        + ", restored_by = @restored_by"
                        + ", restored_when = @restored_when"
                        + ", approved_when = @approved_when"
                        + " WHERE icm_pr_seq = @icm_pr_seq";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string UpdateTotalAndAmountConfirm_Urgent(PurchaseRequestInfo info, IList<PrePurchaseRequestItemInfo> infoListPprItem, IList<PurchaseRequestRouteInfo> infoListRoute)
        {
            PrePurchaseRequestItemDAL dalPpritem = new PrePurchaseRequestItemDAL();
            string returnTxt = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dalPpritem.UpdateAmount(infoListPprItem, dbManager);
                returnTxt = dalPpritem.CheckPurchaseRequestMoneyUrgent(Convert.ToInt32(info.Seq), dbManager);

                if (returnTxt != "1")
                {
                    dbManager.RollBackTransaction();
                    return "กรุณาตรวจสอบวงเงิน";
                }

                Confirm(info, infoListRoute, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
            return returnTxt;
        }

        public string UpdateTotalAndAmountConfirm(PurchaseRequestInfo info, IList<PrePurchaseRequestItemInfo> infoListPprItem, IList<PurchaseRequestRouteInfo> infoListRoute)
        {
            PrePurchaseRequestItemDAL dalPpritem = new PrePurchaseRequestItemDAL();
            string returnTxt = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dalPpritem.UpdateAmount(infoListPprItem, dbManager);
                returnTxt = dalPpritem.CheckPurchaseRequestMoney(Convert.ToInt32(info.Seq), dbManager);

                if (returnTxt != "1")
                {
                    dbManager.RollBackTransaction();
                    return "กรุณาตรวจสอบวงเงินและจำนวน";
                }

                Confirm(info, infoListRoute, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();

            }
            return returnTxt;
        }

        public string UpdateTotalAndAmountConfirmForBidder(PurchaseRequestInfo info, IList<PrePurchaseRequestItemInfo> infoListPprItem, IList<PurchaseRequestRouteInfo> infoListRoute)
        {
            PrePurchaseRequestItemDAL dalPpritem = new PrePurchaseRequestItemDAL();
            string returnTxt = "";
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dalPpritem.UpdateAmountForBidder(infoListPprItem, dbManager);
                returnTxt = dalPpritem.CheckPurchaseRequestMoneyForBidder(Convert.ToInt32(info.Seq), dbManager);

                if (returnTxt != "1")
                {
                    dbManager.RollBackTransaction();
                    return "กรุณาตรวจสอบวงเงินและจำนวน";
                }

                Confirm(info, infoListRoute, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();

            }
            return returnTxt;
        }

        public void Confirm(PurchaseRequestInfo info, IList<PurchaseRequestRouteInfo> infoListRoute, IDBManager dbManagerTrans)
        {
            string strSQL;
            PurchaseRequsteRouteDAL dalRoute = new PurchaseRequsteRouteDAL();
            try
            {
                dbManagerTrans.CreateParameters(4);

                dbManagerTrans.AddParameters(0, "@seq", info.Seq);
                dbManagerTrans.AddParameters(1, "@confirm_emp_seq", info.InfoConfirmEmp.Seq);
                dbManagerTrans.AddParameters(2, "@confirm_date", info.ConfirmDate);
                dbManagerTrans.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);// SystemInfo._ProcessRecordStatus);

                strSQL = "UPDATE ICM_PURCHASE_REQUEST"
                    + " SET CONFIRM_EMP_SEQ = @confirm_emp_seq"
                    + ", CONFIRM_DATE = @confirm_date"
                    + ", RECORD_STATUS = @record_status"
                    + " WHERE ICM_PR_SEQ = @seq";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@created_by", info.InfoConfirmEmp.Seq);
                dbManager.AddParameters(2, "@created_when", DateTime.Now);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                strSQL = "INSERT INTO BGM_PROJECT_OPERATION_REPORT"
                    + "(DESCR, BGM_PROJ_OPER_EXP_SEQ, ICM_PR_ITEM_SEQ, USED_AMOUNT, CAL_AMOUNT, RPT_TYPE, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + "(SELECT DISTINCT (select icm_pr_name from icm_purchase_request where icm_pr_seq = @seq), pri.BGM_PROJ_OPER_EXP_SEQ, ri.ICM_PR_ITEM_SEQ, COALESCE((ri.ICM_PR_ITEM_QTY * ri.ICM_PR_ITEM_PRICE),0)"
                        + ", COALESCE(- (ri.ICM_PR_ITEM_QTY * ri.ICM_PR_ITEM_PRICE),0), 4, @record_status, @created_by, @created_when"
                    + " FROM ICM_PURCHASE_REQUEST_ITEM ri"
                        + " INNER JOIN ICM_PRE_PURCHASE_REQUEST_ITEM pri ON ri.ICM_PR_ITEM_SEQ = pri.ICM_PR_ITEM_SEQ"
                    + " WHERE ri.ICM_PR_SEQ = @seq)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE BGM_PROJECT_OPERATION_REPORT"
                    + " SET CAL_AMOUNT = 0"
                    + ", UPDATED_BY = @created_by"
                    + ", UPDATED_WHEN = @created_when"
                    + " WHERE ICM_PPR_ITEM_SEQ IS NOT NULL"
                        + " AND ICM_PPR_ITEM_SEQ IN"
                            + " (select icm_ppr_item_seq" 
                            + " from icm_pre_purchase_request_item pri"
                                + " inner join icm_purchase_request_item ri"
                            + " where ri.icm_pr_seq = @seq)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dalRoute.Insert(infoListRoute, (int)info.Seq, (int)info.InfoConfirmEmp.Seq, dbManagerTrans);
            }
            catch
            {
                throw;
            }
        }

        public void UpdatePurchaseRequestStatus(int pr_seq, string Status)
        {
            string strSQL;
            PurchaseRequsteRouteDAL dalRoute = new PurchaseRequsteRouteDAL();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@icm_pr_seq", pr_seq);
                dbManager.AddParameters(1, "@record_status", Status);

                strSQL = "UPDATE icm_purchase_request SET record_status = @record_status WHERE icm_pr_seq = @icm_pr_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
